package org.example.Dao;

import org.example.entity.Option;
import org.example.entity.TopAndOpt;
import org.example.entity.Topic;
import org.example.utils.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class TopicDao {

    private OptionDao optionDao = new OptionDao();


    public Topic addTopic(Topic topic) throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "insert into topic (pid,title,`desc`,author,createDate,beginDate,endDate,status,url) values (?,?,?,?,now(),?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, topic.getPid());
        preparedStatement.setString(2, topic.getTitle());
        preparedStatement.setString(3, topic.getDesc());
        preparedStatement.setString(4, topic.getAuthor());
        preparedStatement.setDate(5, new java.sql.Date(topic.getBeginDate().getTime()));
        preparedStatement.setDate(6, new java.sql.Date(topic.getEndDate().getTime()));
        preparedStatement.setInt(7, topic.getStatus());
        preparedStatement.setString(8, topic.getUrl());
        preparedStatement.executeUpdate();
        //把添加到数据内的数据在返回
        sql = "select * from topic  order by tid desc limit 1";
        preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            topic.setTid(resultSet.getInt("tid"));
            topic.setCreateDate(resultSet.getDate("createDate"));
            connection.close();
            preparedStatement.close();
            return topic;
        }
        connection.close();
        preparedStatement.close();
        return null;
    }


    /**
     * 获取主题列表
     *
     * @return 主题列表
     */
    public List<Topic> getTopicList() throws Exception {
        // 1. 更新过期主题状态
        updateExpiredTopics();
        // 2. 查询主题列表（保持原有逻辑）
        Connection connection = DruidUtil.getConnection();
        String sql = "select * from topic where status != 3";
        try (PreparedStatement pstmt = connection.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            List<Topic> topicList = new ArrayList<>();
            while (rs.next()) {
                Topic topic = new Topic();
                topic.setTid(rs.getInt("tid"));
                topic.setPid(rs.getInt("pid"));
                topic.setTitle(rs.getString("title"));
                topic.setDesc(rs.getString("desc"));
                topic.setAuthor(rs.getString("author"));
                topic.setCreateDate(rs.getDate("createDate"));
                topic.setBeginDate(rs.getDate("beginDate"));
                topic.setEndDate(rs.getDate("endDate"));
                topic.setStatus(rs.getInt("status"));
                topic.setUrl(rs.getString("url"));
                topicList.add(topic);
            }
            return topicList;
        } finally {
            // 资源会被try-with-resources自动关闭
        }
    }

    // 更新过期主题的状态
    private void updateExpiredTopics() throws Exception {
        Connection connection = DruidUtil.getConnection();

        String sql = "UPDATE topic SET status = 2 WHERE status != 3   AND endDate < CURDATE()";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int count = preparedStatement.executeUpdate();
        System.out.println("update expired topics ==> " + count);
    }


//    public List<Topic> getTopicList() throws Exception {
//        Connection connection = DruidUtil.getConnection();
//        String sql = "select * from topic where status != 3";
//        PreparedStatement preparedStatement = connection.prepareStatement(sql);
//        ResultSet resultSet = preparedStatement.executeQuery();
//        List<Topic> topicList = new ArrayList<>();
//        while (resultSet.next()) {
//            Topic topic = new Topic();
//            topic.setTid(resultSet.getInt("tid"));
//            topic.setPid(resultSet.getInt("pid"));
//            topic.setTitle(resultSet.getString("title"));
//            topic.setDesc(resultSet.getString("desc"));
//            topic.setAuthor(resultSet.getString("author"));
//            topic.setCreateDate(resultSet.getDate("createDate"));
//            topic.setBeginDate(resultSet.getDate("beginDate"));
//            topic.setEndDate(resultSet.getDate("endDate"));
//            topic.setStatus(resultSet.getInt("status"));
//            topic.setUrl(resultSet.getString("url"));
//            topicList.add(topic);
//        }
//        connection.close();
//        preparedStatement.close();
//        return topicList;
//
//    }

    public Topic getTopic(int tid) throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "select * from topic where tid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, tid);
        ResultSet resultSet = preparedStatement.executeQuery();
        Topic topic = null;
        if (resultSet.next()) {
            topic = new Topic();
            topic.setTid(resultSet.getInt("tid"));
            topic.setPid(resultSet.getInt("pid"));
            topic.setTitle(resultSet.getString("title"));
            topic.setDesc(resultSet.getString("desc"));
            topic.setAuthor(resultSet.getString("author"));
            topic.setCreateDate(resultSet.getDate("createDate"));
            topic.setBeginDate(resultSet.getDate("beginDate"));
            topic.setEndDate(resultSet.getDate("endDate"));
            topic.setStatus(resultSet.getInt("status"));
            topic.setUrl(resultSet.getString("url"));
        }
        connection.close();
        preparedStatement.close();
        return topic;
    }


    public List<Option> getOptionList(int tid) throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "select * from `option` where tid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, tid);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Option> optionList = new ArrayList<>();
        while (resultSet.next()) {
            Option option = new Option();
            option.setOid(resultSet.getInt("oid"));
            option.setTid(resultSet.getInt("tid"));
            option.setUrl(resultSet.getString("url"));
            option.setName(resultSet.getString("name"));
            optionList.add(option);
        }
        connection.close();
        preparedStatement.close();
        return optionList;
    }

    /**
     * 获取主题和选项
     *
     * @param tid 主题id
     * @return 主题和选项
     */
    public TopAndOpt getTopicAndOption(int tid) throws Exception {
        TopAndOpt topAndOpt = new TopAndOpt();
        topAndOpt.setTopic(getTopic(tid));
        topAndOpt.setOptions(getOptionList(tid));
        return topAndOpt;
    }

    public int deleteOption(int tid) throws Exception {


        Connection connection = DruidUtil.getConnection();
        String sql = "delete from `option` where tid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, tid);
        int count = preparedStatement.executeUpdate();
        connection.close();
        preparedStatement.close();
        return count;
    }


    public int updateTopic(Topic topic) throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "update topic set pid = ?,title = ?,`desc` = ?,author = ?,beginDate = ?,endDate = ?,status = ?,url = ? where tid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, topic.getPid());
        preparedStatement.setString(2, topic.getTitle());
        preparedStatement.setString(3, topic.getDesc());
        preparedStatement.setString(4, topic.getAuthor());
        preparedStatement.setDate(5, new java.sql.Date(topic.getBeginDate().getTime()));
        preparedStatement.setDate(6, new java.sql.Date(topic.getEndDate().getTime()));
        preparedStatement.setInt(7, topic.getStatus());
        preparedStatement.setString(8, topic.getUrl());
        preparedStatement.setInt(9, topic.getTid());
        int count = preparedStatement.executeUpdate();
        connection.close();
        preparedStatement.close();
        return count;
    }


    public int addTopicAndOption(TopAndOpt topAndOpt) throws Exception {
        //先删除旧数据
        //删除所有的相关option选项
        int delCount = deleteOption(topAndOpt.getTopic().getTid());
        System.out.println("delete options count ==> " + delCount);
        //插入新的数据
        int addCount = optionDao.addOptions(topAndOpt.getTopic().getTid(), topAndOpt.getOptions());
        System.out.println("add new  options count ==> " + addCount);
        // 开始更新topic数据
        int updateCount = updateTopic(topAndOpt.getTopic());
        System.out.println("update topic count ==> " + updateCount);
        return (delCount + addCount + updateCount);
    }

    /**
     * 删除topic 实质上是修改状态为3
     *
     * @param tid
     */
    public int delTopic(int tid) throws Exception {

        Connection connection = DruidUtil.getConnection();
        String sql = "update topic set status = 3 where tid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, tid);
        int count = preparedStatement.executeUpdate();
        connection.close();
        preparedStatement.close();
        return count;
    }

    /**
     * 条件查询主题
     *
     * @param tid       主题id
     * @param title     主题标题
     * @param beginDate 开始时间
     * @param endDate   结束时间
     * @param status    状态
     * @return 主题列表
     */
    public List<Topic> find(int tid, String title, Date beginDate, Date endDate, int status) throws Exception {
        List<Topic> topicList = new ArrayList<>();
        String sql = "SELECT * FROM topic WHERE 1=1";
        List<Object> params = new ArrayList<>();

        Connection connection = DruidUtil.getConnection();
        StringBuilder sb = new StringBuilder(sql);

        if (tid != -1) {
            sb.append(" AND tid = ?");
            params.add(tid);
        }

        if (title != null && !title.isEmpty()) {
            sb.append(" AND title LIKE ?");
            params.add("%" + title + "%");
        }

        if (beginDate != null) {
            sb.append(" AND beginDate >= ?");
            params.add(beginDate);
        }

        if (endDate != null) {
            sb.append(" AND endDate <= ?");
            params.add(endDate);
        }

        if (status != -1) {
            sb.append(" AND status = ?");
            params.add(status);
        }

        sql = sb.toString();
        System.out.println("find topic list sql ==> " + sql);

        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(i + 1, params.get(i));
            }

            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    Topic topic = new Topic();
                    topic.setTid(resultSet.getInt("tid"));
                    topic.setPid(resultSet.getInt("pid"));
                    topic.setTitle(resultSet.getString("title"));
                    topic.setDesc(resultSet.getString("desc"));
                    topic.setAuthor(resultSet.getString("author"));
                    topic.setBeginDate(resultSet.getDate("beginDate"));
                    topic.setEndDate(resultSet.getDate("endDate"));
                    topic.setStatus(resultSet.getInt("status"));
                    topic.setUrl(resultSet.getString("url"));
                    topicList.add(topic);
                }
            }
            connection.close();
        }

        return topicList;
    }
}
